using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VBPACK
    {
        public DataRow Convert2BPACK(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryBPACK(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["BPAMANDANT"] = " ".PadRight(2 ); 
         dwReturn["BPALIEFSCH"] = " ".PadRight(8 ); 
         dwReturn["BPAPACKNR"] = " ".PadRight(3 ); 
         dwReturn["BPAPACKPOS"] = " ".PadRight(3 ); 
         dwReturn["BPAAUFTYP"] = " ".PadRight(1 ); 
         dwReturn["BPAAUFKRS"] = " ".PadRight(2 ); 
         dwReturn["BPAAUFLFD"] = " ".PadRight(6 ); 
         dwReturn["BPAAUFPOS"] = " ".PadRight(3 ); 
         dwReturn["BPATEILNR"] = " ".PadRight(4 ); 
         dwReturn["BPABAUMKRS"] = " ".PadRight(2 ); 
         dwReturn["BPABAUMLFD"] = " ".PadRight(6 ); 
         dwReturn["BPALO"] = " ".PadRight(2 ); 
         dwReturn["BPALB"] = " ".PadRight(2 ); 
         dwReturn["BPALP"] = " ".PadRight(8 ); 
         dwReturn["BPAAUFBKZ"] = " ".PadRight(1 ); 
         dwReturn["BPAKOLLINR"] = " ".PadRight(8 ); 
         dwReturn["BPAPRODLO"] = " ".PadRight(2 ); 
         dwReturn["BPAPRODLB"] = " ".PadRight(2 ); 
         dwReturn["BPAPRODLP"] = " ".PadRight(8 ); 
         dwReturn["BPAPARTIE"] = " ".PadRight(7 ); 
         dwReturn["BPAPARTIEKZ"] = " ".PadRight(1 ); 
         dwReturn["BPASPPART"] = " ".PadRight(10 ); 
         dwReturn["BPAKART"] = " ".PadRight(20 ); 
         dwReturn["BPAKVAR"] = " ".PadRight(2 ); 
         dwReturn["BPAFARBE"] = " ".PadRight(10 ); 
         dwReturn["BPAZUSTAND"] = " ".PadRight(4 ); 
         dwReturn["BPAFERTKZ"] = " ".PadRight(1 ); 
         dwReturn["BPAZENSUR"] = " ".PadRight(1 ); 
         dwReturn["BPASCHLICHTGR"] = " ".PadRight(5 ); 
         dwReturn["BPASCHLICHTNR"] = " ".PadRight(15 ); 
         dwReturn["BPAKANTE"] = " ".PadRight(2 ); 
         dwReturn["BPAKOMBIKZ"] = " ".PadRight(1 ); 
         dwReturn["BPATAX"] = " ".PadRight(3 ); 
         dwReturn["BPAFDZ"] = " ".PadRight(0 ); 
         dwReturn["BPAPICKUP"] = " ".PadRight(0 ); 
         dwReturn["BPAANZTB"] = " ".PadRight(0 ); 
         dwReturn["BPASOLLLG"] = " ".PadRight(0 ); 
         dwReturn["BPASOLLGEW"] = " ".PadRight(0 ); 
         dwReturn["BPASOLLVLG"] = " ".PadRight(0 ); 
         dwReturn["BPASOLLSTK"] = " ".PadRight(0 ); 
         dwReturn["BPAISTLG"] = " ".PadRight(0 ); 
         dwReturn["BPAISTGEW"] = " ".PadRight(0 ); 
         dwReturn["BPAISTVLG"] = " ".PadRight(0 ); 
         dwReturn["BPAISTSTK"] = " ".PadRight(0 ); 
         dwReturn["BPARESTLG"] = " ".PadRight(0 ); 
         dwReturn["BPARESTGEW"] = " ".PadRight(0 ); 
         dwReturn["BPAKGNET"] = " ".PadRight(0 ); 
         dwReturn["BPAKGBRUT"] = " ".PadRight(0 ); 
         dwReturn["BPATARA"] = " ".PadRight(0 ); 
         dwReturn["BPABAUMTYP"] = " ".PadRight(6 ); 
         dwReturn["BPABEM"] = " ".PadRight(40 ); 
         dwReturn["BPAAUSFFRB"] = " ".PadRight(0 ); 
         dwReturn["BPAMNRKRS"] = " ".PadRight(2 ); 
         dwReturn["BPAMLFDNR"] = " ".PadRight(4 ); 
         dwReturn["BPAMPOS"] = " ".PadRight(2 ); 
         dwReturn["BPADL"] = " ".PadRight(1 ); 
         dwReturn["BPAFILLER"] = " ".PadRight(66 ); 
         dwReturn["BPAAENZL"] = " ".PadRight(0 ); 
         dwReturn["BPAAENB"] = " ".PadRight(8 ); 
         dwReturn["BPAAEND"] = " ".PadRight(0 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapBPACK(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveBPACK(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryBPACK(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapBPACK(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertBPACK(dwLocal, ITXDBVERSION);
            }
            UpdateBPACK(dwLocal);
        }
        public void InsertBPACK(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO BPACK(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryBPACK(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM BPACK WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM BPACK  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "BPACK";
            Return = Result;
            return Return;
        }
        public void DeleteBPACK(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM BPACK   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateBPACK(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE BPACK SET "
                    +"BPAMANDANT= :BPAMANDANT,"
                    +"BPALIEFSCH= :BPALIEFSCH,"
                    +"BPAPACKNR= :BPAPACKNR,"
                    +"BPAPACKPOS= :BPAPACKPOS,"
                    +"BPAAUFTYP= :BPAAUFTYP,"
                    +"BPAAUFKRS= :BPAAUFKRS,"
                    +"BPAAUFLFD= :BPAAUFLFD,"
                    +"BPAAUFPOS= :BPAAUFPOS,"
                    +"BPATEILNR= :BPATEILNR,"
                    +"BPABAUMKRS= :BPABAUMKRS,"
                    +"BPABAUMLFD= :BPABAUMLFD,"
                    +"BPALO= :BPALO,"
                    +"BPALB= :BPALB,"
                    +"BPALP= :BPALP,"
                    +"BPAAUFBKZ= :BPAAUFBKZ,"
                    +"BPAKOLLINR= :BPAKOLLINR,"
                    +"BPAPRODLO= :BPAPRODLO,"
                    +"BPAPRODLB= :BPAPRODLB,"
                    +"BPAPRODLP= :BPAPRODLP,"
                    +"BPAPARTIE= :BPAPARTIE,"
                    +"BPAPARTIEKZ= :BPAPARTIEKZ,"
                    +"BPASPPART= :BPASPPART,"
                    +"BPAKART= :BPAKART,"
                    +"BPAKVAR= :BPAKVAR,"
                    +"BPAFARBE= :BPAFARBE,"
                    +"BPAZUSTAND= :BPAZUSTAND,"
                    +"BPAFERTKZ= :BPAFERTKZ,"
                    +"BPAZENSUR= :BPAZENSUR,"
                    +"BPASCHLICHTGR= :BPASCHLICHTGR,"
                    +"BPASCHLICHTNR= :BPASCHLICHTNR,"
                    +"BPAKANTE= :BPAKANTE,"
                    +"BPAKOMBIKZ= :BPAKOMBIKZ,"
                    +"BPATAX= :BPATAX,"
                    +"BPAFDZ= :BPAFDZ,"
                    +"BPAPICKUP= :BPAPICKUP,"
                    +"BPAANZTB= :BPAANZTB,"
                    +"BPASOLLLG= :BPASOLLLG,"
                    +"BPASOLLGEW= :BPASOLLGEW,"
                    +"BPASOLLVLG= :BPASOLLVLG,"
                    +"BPASOLLSTK= :BPASOLLSTK,"
                    +"BPAISTLG= :BPAISTLG,"
                    +"BPAISTGEW= :BPAISTGEW,"
                    +"BPAISTVLG= :BPAISTVLG,"
                    +"BPAISTSTK= :BPAISTSTK,"
                    +"BPARESTLG= :BPARESTLG,"
                    +"BPARESTGEW= :BPARESTGEW,"
                    +"BPAKGNET= :BPAKGNET,"
                    +"BPAKGBRUT= :BPAKGBRUT,"
                    +"BPATARA= :BPATARA,"
                    +"BPABAUMTYP= :BPABAUMTYP,"
                    +"BPABEM= :BPABEM,"
                    +"BPAAUSFFRB= :BPAAUSFFRB,"
                    +"BPAMNRKRS= :BPAMNRKRS,"
                    +"BPAMLFDNR= :BPAMLFDNR,"
                    +"BPAMPOS= :BPAMPOS,"
                    +"BPADL= :BPADL,"
                    +"BPAFILLER= :BPAFILLER,"
                    +"BPAAENZL= :BPAAENZL,"
                    +"BPAAENB= :BPAAENB,"
                    +"BPAAEND= :BPAAEND,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("BPAMANDANT", OracleType.NVarChar).Value = dwData["BPAMANDANT"].ToString(); 
          or1.Parameters.Add("BPALIEFSCH", OracleType.NVarChar).Value = dwData["BPALIEFSCH"].ToString(); 
          or1.Parameters.Add("BPAPACKNR", OracleType.NVarChar).Value = dwData["BPAPACKNR"].ToString(); 
          or1.Parameters.Add("BPAPACKPOS", OracleType.NVarChar).Value = dwData["BPAPACKPOS"].ToString(); 
          or1.Parameters.Add("BPAAUFTYP", OracleType.NVarChar).Value = dwData["BPAAUFTYP"].ToString(); 
          or1.Parameters.Add("BPAAUFKRS", OracleType.NVarChar).Value = dwData["BPAAUFKRS"].ToString(); 
          or1.Parameters.Add("BPAAUFLFD", OracleType.NVarChar).Value = dwData["BPAAUFLFD"].ToString(); 
          or1.Parameters.Add("BPAAUFPOS", OracleType.NVarChar).Value = dwData["BPAAUFPOS"].ToString(); 
          or1.Parameters.Add("BPATEILNR", OracleType.NVarChar).Value = dwData["BPATEILNR"].ToString(); 
          or1.Parameters.Add("BPABAUMKRS", OracleType.NVarChar).Value = dwData["BPABAUMKRS"].ToString(); 
          or1.Parameters.Add("BPABAUMLFD", OracleType.NVarChar).Value = dwData["BPABAUMLFD"].ToString(); 
          or1.Parameters.Add("BPALO", OracleType.NVarChar).Value = dwData["BPALO"].ToString(); 
          or1.Parameters.Add("BPALB", OracleType.NVarChar).Value = dwData["BPALB"].ToString(); 
          or1.Parameters.Add("BPALP", OracleType.NVarChar).Value = dwData["BPALP"].ToString(); 
          or1.Parameters.Add("BPAAUFBKZ", OracleType.NVarChar).Value = dwData["BPAAUFBKZ"].ToString(); 
          or1.Parameters.Add("BPAKOLLINR", OracleType.NVarChar).Value = dwData["BPAKOLLINR"].ToString(); 
          or1.Parameters.Add("BPAPRODLO", OracleType.NVarChar).Value = dwData["BPAPRODLO"].ToString(); 
          or1.Parameters.Add("BPAPRODLB", OracleType.NVarChar).Value = dwData["BPAPRODLB"].ToString(); 
          or1.Parameters.Add("BPAPRODLP", OracleType.NVarChar).Value = dwData["BPAPRODLP"].ToString(); 
          or1.Parameters.Add("BPAPARTIE", OracleType.NVarChar).Value = dwData["BPAPARTIE"].ToString(); 
          or1.Parameters.Add("BPAPARTIEKZ", OracleType.NVarChar).Value = dwData["BPAPARTIEKZ"].ToString(); 
          or1.Parameters.Add("BPASPPART", OracleType.NVarChar).Value = dwData["BPASPPART"].ToString(); 
          or1.Parameters.Add("BPAKART", OracleType.NVarChar).Value = dwData["BPAKART"].ToString(); 
          or1.Parameters.Add("BPAKVAR", OracleType.NVarChar).Value = dwData["BPAKVAR"].ToString(); 
          or1.Parameters.Add("BPAFARBE", OracleType.NVarChar).Value = dwData["BPAFARBE"].ToString(); 
          or1.Parameters.Add("BPAZUSTAND", OracleType.NVarChar).Value = dwData["BPAZUSTAND"].ToString(); 
          or1.Parameters.Add("BPAFERTKZ", OracleType.NVarChar).Value = dwData["BPAFERTKZ"].ToString(); 
          or1.Parameters.Add("BPAZENSUR", OracleType.NVarChar).Value = dwData["BPAZENSUR"].ToString(); 
          or1.Parameters.Add("BPASCHLICHTGR", OracleType.NVarChar).Value = dwData["BPASCHLICHTGR"].ToString(); 
          or1.Parameters.Add("BPASCHLICHTNR", OracleType.NVarChar).Value = dwData["BPASCHLICHTNR"].ToString(); 
          or1.Parameters.Add("BPAKANTE", OracleType.NVarChar).Value = dwData["BPAKANTE"].ToString(); 
          or1.Parameters.Add("BPAKOMBIKZ", OracleType.NVarChar).Value = dwData["BPAKOMBIKZ"].ToString(); 
          or1.Parameters.Add("BPATAX", OracleType.NVarChar).Value = dwData["BPATAX"].ToString(); 
          or1.Parameters.Add("BPAFDZ", OracleType.NVarChar).Value = dwData["BPAFDZ"].ToString(); 
          or1.Parameters.Add("BPAPICKUP", OracleType.NVarChar).Value = dwData["BPAPICKUP"].ToString(); 
          or1.Parameters.Add("BPAANZTB", OracleType.NVarChar).Value = dwData["BPAANZTB"].ToString(); 
          or1.Parameters.Add("BPASOLLLG", OracleType.NVarChar).Value = dwData["BPASOLLLG"].ToString(); 
          or1.Parameters.Add("BPASOLLGEW", OracleType.NVarChar).Value = dwData["BPASOLLGEW"].ToString(); 
          or1.Parameters.Add("BPASOLLVLG", OracleType.NVarChar).Value = dwData["BPASOLLVLG"].ToString(); 
          or1.Parameters.Add("BPASOLLSTK", OracleType.NVarChar).Value = dwData["BPASOLLSTK"].ToString(); 
          or1.Parameters.Add("BPAISTLG", OracleType.NVarChar).Value = dwData["BPAISTLG"].ToString(); 
          or1.Parameters.Add("BPAISTGEW", OracleType.NVarChar).Value = dwData["BPAISTGEW"].ToString(); 
          or1.Parameters.Add("BPAISTVLG", OracleType.NVarChar).Value = dwData["BPAISTVLG"].ToString(); 
          or1.Parameters.Add("BPAISTSTK", OracleType.NVarChar).Value = dwData["BPAISTSTK"].ToString(); 
          or1.Parameters.Add("BPARESTLG", OracleType.NVarChar).Value = dwData["BPARESTLG"].ToString(); 
          or1.Parameters.Add("BPARESTGEW", OracleType.NVarChar).Value = dwData["BPARESTGEW"].ToString(); 
          or1.Parameters.Add("BPAKGNET", OracleType.NVarChar).Value = dwData["BPAKGNET"].ToString(); 
          or1.Parameters.Add("BPAKGBRUT", OracleType.NVarChar).Value = dwData["BPAKGBRUT"].ToString(); 
          or1.Parameters.Add("BPATARA", OracleType.NVarChar).Value = dwData["BPATARA"].ToString(); 
          or1.Parameters.Add("BPABAUMTYP", OracleType.NVarChar).Value = dwData["BPABAUMTYP"].ToString(); 
          or1.Parameters.Add("BPABEM", OracleType.NVarChar).Value = dwData["BPABEM"].ToString(); 
          or1.Parameters.Add("BPAAUSFFRB", OracleType.NVarChar).Value = dwData["BPAAUSFFRB"].ToString(); 
          or1.Parameters.Add("BPAMNRKRS", OracleType.NVarChar).Value = dwData["BPAMNRKRS"].ToString(); 
          or1.Parameters.Add("BPAMLFDNR", OracleType.NVarChar).Value = dwData["BPAMLFDNR"].ToString(); 
          or1.Parameters.Add("BPAMPOS", OracleType.NVarChar).Value = dwData["BPAMPOS"].ToString(); 
          or1.Parameters.Add("BPADL", OracleType.NVarChar).Value = dwData["BPADL"].ToString(); 
          or1.Parameters.Add("BPAFILLER", OracleType.NVarChar).Value = dwData["BPAFILLER"].ToString(); 
          or1.Parameters.Add("BPAAENZL", OracleType.NVarChar).Value = dwData["BPAAENZL"].ToString(); 
          or1.Parameters.Add("BPAAENB", OracleType.NVarChar).Value = dwData["BPAAENB"].ToString(); 
          or1.Parameters.Add("BPAAEND", OracleType.NVarChar).Value = dwData["BPAAEND"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
